use master
go
create database Student
on
(
	name='Student',
	filename='E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\.mdf',
	size=5MB,
	maxsize=20MB,
	filegrowth=10%
)
log on
(
	name='Student_log',
	filename='E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\.ldf',
	size=6MB,
	maxsize=28MB,
	filegrowth=3MB
)
go


use Student
go
create table Class
(
   ClassID int primary key identity,
   ClassName nvarchar(20) unique not null
)
insert into Class (ClassName) values ('一年级'),('二年级'),('三年级'),('四年级'),('五年级'),('六年级'),('初一'),('初二'),('初三'),('高一')
update Class set ClassName='被修改了名称的一年级' where ClassID=1
delete from Class where ClassID=10

go

create table Student01
(
	StuID int primary key identity(1,1),
	ClassID int foreign key references Class(ClassID),
	StuName nvarchar(20) not null,
	StuSex nvarchar(1) default('男') check(StuSex in('男','女')),
	StuBirthday date ,
	StuPhone nvarchar(11) unique ,
	StuAddress nvarchar(200) 
)

insert into Student01 (ClassID,StuName,StuSex,StuPhone) values 
(1,'赵一','男',12345678910),
(2,'钱二','女',12345678911),
(3,'张三','男',12345678912),
(4,'李四','女',12345678913),
(5,'王五','男',12345678914),
(6,'孙六','女',12345678915),
(7,'周七','男',12345678916),
(8,'吴八','女',12345678917),
(9,'冯九','男',12345678918),
(1,'陈十','女',12345678919),
(2,'佩奇','男',12345678920),
(3,'乔治','女',12345678921),
(4,'格格巫','男',12345678922),
(5,'蓝精灵','女',12345678923),
(6,'dio','男',12345678924),
(7,'唐僧','女',12345678925),
(8,'悟空','男',12345678926),
(9,'八戒','女',12345678927),
(1,'沙僧','男',12345678928),
(2,'白龙马','女',12345678929)

alter table Student01 add CreateDate datetime default(getdate())
update Student01 set CreateDate=getdate()
delete from Student01 where ClassID=5

go

create table Course
(
	CourseID int primary key identity,
	CourseName nvarchar(50) unique not null,
	CourseCredit int not null default(1) check(CourseCredit>0 and CourseCredit<6),
	CourseType nvarchar(10) check(CourseType='专业课' or CourseType='公共课')
)
insert into Course (CourseName) values ('体育课'),('高数课'),
('职素课'),('专业课'),
('英语课'),('思修课')

select CourseName from Course

update Course set CourseCredit=2 where CourseID=1

go

create table Score01
(
	ScoreID int primary key identity,
	StuID int foreign key references Student01(StuID),
	CourseID int foreign key references Course(CourseID),
	Score decimal(5,2) unique not null
)
alter table Score add constraint FK_Score_Score check(Score>=0 and Score<=100)

alter table Score add constraint DK_Score_Score default(0) for Score

insert into Score01(StuID,CourseID,Score) values 
(1,1,60),(2,2,61),
(3,3,62),(4,4,63),
(5,5,64),(6,6,65),
(7,1,66),(8,2,67),
(10,4,69),
(11,5,70),(12,6,71),
(13,1,72),(14,2,73),
(15,3,74),(16,4,75),
(17,5,76),
(19,1,78),(20,2,79)

delete from Score01 where StuID=1

delete from Score01 where CourseID=1

go